
/*********************
 **  CreateTime: 2006-7-26 
 **********************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using EntLibEC.Cmn;
using EntLibEC.Info.PIM;
using EntLibEC.Dac.PIM;



namespace EntLibEC.Dac.PIM
{
    /// <summary>
    /// Contains the the data access methods for CategoryTemplatePropertyDA.
    /// </summary>
    public abstract class CategoryTemplatePropertyDA
    {
        #region Sql Script

        private const string SQL_GET_CATEGORYTEMPLATE_ALL_PROPERTIES
            = @"SELECT a.SysNo,a.CategoryTemplateSysNo,a.PropertySysNo,a.GroupSysNo,a.IsInAdvSearch,a.WebDisplayStyle,a.Priority,a.LastEditUserSysNo,a.LastEditTime,
                    b.UserName AS LastEditUsername, c.PropertyDescription AS PropertyName, d.GroupDescription AS GroupName
				FROM PIM_CategoryTemplateProperty a (NOLOCK)
                    LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
                    LEFT JOIN PIM_Property c (NOLOCK) ON a.PropertySysNo = c.SysNo
                    LEFT JOIN PIM_PropertyGroup d (NOLOCK) ON a.GroupSysNo = d.SysNo
				WHERE CategoryTemplateSysNo = @CategoryTemplateSysNo
                ORDER BY a.Priority ASC";

        private const string SQL_GET_CATEGORYTEMPLATEPROPERTY
            = @"SELECT a.SysNo,a.CategoryTemplateSysNo,a.PropertySysNo,a.GroupSysNo,a.IsInAdvSearch,a.WebDisplayStyle,a.Priority,a.LastEditUserSysNo,a.LastEditTime,
                    b.UserName AS LastEditUsername, c.PropertyDescription AS PropertyName, d.GroupDescription AS GroupName
				FROM PIM_CategoryTemplateProperty a (NOLOCK)
                    LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
                    LEFT JOIN PIM_Property c (NOLOCK) ON a.PropertySysNo = c.SysNo
                    LEFT JOIN PIM_PropertyGroup d (NOLOCK) ON a.GroupSysNo = d.SysNo
				WHERE SysNo = @SysNo";

        private const string SQL_DELETE_ALL_CATEGORYTEMPLATEPROPERTY
            = @"DELETE FROM PIM_CategoryTemplateProperty WHERE CategoryTemplateSysNo = @CategoryTemplateSysNo";

        private const string SQL_INSERT_CATEGORYTEMPLATEPROPERTY = @"INSERT INTO PIM_CategoryTemplateProperty(CategoryTemplateSysNo,PropertySysNo,GroupSysNo,IsInAdvSearch,WebDisplayStyle,Priority,LastEditUserSysNo,LastEditTime) VALUES (@CategoryTemplateSysNo,@PropertySysNo,@GroupSysNo,@IsInAdvSearch,@WebDisplayStyle,@Priority,@LastEditUserSysNo,getdate()) SELECT @@IDENTITY";
        private const string SQL_UPDATE_CATEGORYTEMPLATEPROPERTY = @"UPDATE PIM_CategoryTemplateProperty SET CategoryTemplateSysNo=@CategoryTemplateSysNo,GroupSysNo=@GroupSysNo,IsInAdvSearch=@IsInAdvSearch,WebDisplayStyle=@WebDisplayStyle,Priority=@Priority,LastEditUserSysNo=@LastEditUserSysNo,LastEditTime=getdate() WHERE SysNo=@SysNo";
        private const string SQL_DELETE_CATEGORYTEMPLATEPROPERTY = @"DELETE FROM PIM_CategoryTemplateProperty WHERE SysNo = @SysNo";
        private const string SQL_PRIORITY_EXIST = @"SELECT SysNo FROM PIM_CategoryTemplateProperty a (NOLOCK) WHERE CategoryTemplateSysNo = @CategoryTemplateSysNo AND Priority=@Priority AND SysNo<>@SysNo";
        #endregion

        public static bool PriorityExist(CategoryTemplatePropertyInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@CategoryTemplateSysNo", SqlDbType.Int),
                new SqlParameter("@Priority", SqlDbType.NVarChar, 100)
            };


            parms[0].Value = info.SysNo;
            parms[1].Value = info.CategoryTemplateSysNo;
            parms[2].Value = info.Priority;


            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_PRIORITY_EXIST, parms);
            if (obj == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        protected static CategoryTemplatePropertyInfo Map(DataRow row)
        {
            CategoryTemplatePropertyInfo info = new CategoryTemplatePropertyInfo();

            info.SysNo = Convert.ToInt32(row["SysNo"]);
            info.CategoryTemplateSysNo = Convert.ToInt32(row["CategoryTemplateSysNo"]);
            info.PropertySysNo = Convert.ToInt32(row["PropertySysNo"]);
            info.GroupName = row["GroupName"].ToString();
            info.IsInAdvSearch = Convert.ToInt32(row["IsInAdvSearch"]) == 1;
            info.WebDisplayStyle = (PropertyWebDisplayStyle)Convert.ToInt32(row["WebDisplayStyle"]);
            info.Priority = Convert.ToInt32(row["Priority"]);
            info.LastEditUserSysNo = Convert.ToInt32(row["LastEditUserSysNo"]);
            info.LastEditTime = Convert.ToDateTime(row["LastEditTime"]);

            info.LastEditUsername = row["LastEditUsername"].ToString();
            info.PropertyName = row["PropertyName"].ToString();
            info.GroupName = row["GroupName"].ToString();

            return info;
        }
        
        public static CategoryTemplatePropertyInfo GetCategoryTemplateProperty(int sysNo)
        {
            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@SysNo", SqlDbType.Int);
            parms[0].Value = sysNo;

            DataTable dt = DataAccessFacade.GetDataTable(SQL_GET_CATEGORYTEMPLATE_ALL_PROPERTIES, parms);
            if (dt == null || dt.Rows.Count != 1)
                return null;

            return Map(dt.Rows[0]);
        }

        public static List<CategoryTemplatePropertyInfo> GetCategoryTemplateProperties(int sysNo)
        {
            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@CategoryTemplateSysNo", SqlDbType.Int);
            parms[0].Value = sysNo;

            DataTable dt = DataAccessFacade.GetDataTable(SQL_GET_CATEGORYTEMPLATE_ALL_PROPERTIES, parms);
            if (dt == null || dt.Rows.Count == 0)
                return null;

            List<CategoryTemplatePropertyInfo> list = new List<CategoryTemplatePropertyInfo>();
            foreach (DataRow row in dt.Rows)
                list.Add(Map(row));

            return list;
        }

        public static int InsertCategoryTemplateProperty(CategoryTemplatePropertyInfo info)
        {
            SqlParameter[] parms = new SqlParameter[7];

            parms[0] = new SqlParameter("@CategoryTemplateSysNo", SqlDbType.Int);
            parms[1] = new SqlParameter("@PropertySysNo", SqlDbType.Int);
            parms[2] = new SqlParameter("@GroupSysNo", SqlDbType.Int);
            parms[3] = new SqlParameter("@IsInAdvSearch", SqlDbType.Int);
            parms[4] = new SqlParameter("@WebDisplayStyle", SqlDbType.Int);
            parms[5] = new SqlParameter("@Priority", SqlDbType.Int);
            parms[6] = new SqlParameter("@LastEditUserSysNo", SqlDbType.Int);

            parms[0].Value = info.CategoryTemplateSysNo;
            parms[1].Value = info.PropertySysNo;
            parms[2].Value = info.GroupSysNo;
            parms[3].Value = info.IsInAdvSearch;
            parms[4].Value = info.WebDisplayStyle;
            parms[5].Value = info.Priority;
            parms[6].Value = info.LastEditUserSysNo;

            int sysNo = Convert.ToInt32(DataAccessFacade.ExecScalarSqlCommand(SQL_INSERT_CATEGORYTEMPLATEPROPERTY, parms));
            info.SysNo = sysNo;
            return sysNo;
        }

        public static void UpdateCategoryTemplateProperty(CategoryTemplatePropertyInfo info)
        {
            SqlParameter[] parms = new SqlParameter[7];

            parms[0] = new SqlParameter("@CategoryTemplateSysNo", SqlDbType.Int);
            parms[1] = new SqlParameter("@SysNo", SqlDbType.Int);
            parms[2] = new SqlParameter("@GroupSysNo", SqlDbType.Int);
            parms[3] = new SqlParameter("@IsInAdvSearch", SqlDbType.Int);
            parms[4] = new SqlParameter("@WebDisplayStyle", SqlDbType.Int);
            parms[5] = new SqlParameter("@Priority", SqlDbType.Int);
            parms[6] = new SqlParameter("@LastEditUserSysNo", SqlDbType.Int);

            parms[0].Value = info.CategoryTemplateSysNo;
            parms[1].Value = info.SysNo;
            parms[2].Value = info.GroupSysNo;
            parms[3].Value = info.IsInAdvSearch ? 1 : 0;
            parms[4].Value =(PropertyWebDisplayStyle)Enum.Parse(typeof(PropertyWebDisplayStyle),info.WebDisplayStyle.ToString());
            parms[5].Value = info.Priority;
            parms[6].Value = info.LastEditUserSysNo;

            DataAccessFacade.ExecSqlCommand(SQL_UPDATE_CATEGORYTEMPLATEPROPERTY, parms);
        }

        public static void DeleteCategoryTemplateProperty(int SysNo)
        {
            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@SysNo", SqlDbType.Int);
            parms[0].Value = SysNo;

            DataAccessFacade.ExecSqlCommand(SQL_DELETE_CATEGORYTEMPLATEPROPERTY, parms);
        }

        public static void DeleteAllCategoryTemplateProperties(int categoryTemplateSysNo)
        {
            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@CategoryTemplateSysNo", SqlDbType.Int);
            parms[0].Value = categoryTemplateSysNo;

            DataAccessFacade.ExecSqlCommand(SQL_DELETE_ALL_CATEGORYTEMPLATEPROPERTY, parms);
        }
    }
}
